#coding:utf-8
import pandas as pd
import csv
import numpy as np
from datetime import datetime
import pymysql
#读取文件
# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="hui123456", db='dbtest')
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用execute()方法执行SQL查询
#cursor.execute("create database dbtest charset=utf8;")
# 使用 fetchone() 方法获取单条数据

sql="""
     	SELECT order2 , Product_ID ,Product_xg,Product_BianMa,Product_num,Product_price,liulianglaiyuan,daren,guanggao,liuliangleixing,liuliangqudao,liuliangticai,day,yh_pingtai,yh_daren,shop,order_zt,order_sh ,fuai,dianzhuguo from doudian_day 
        where  month(day)=9 and day!="1998-01-01"
		 
     """
cursor.execute(sql)
result=cursor.fetchall()
dou=pd.DataFrame(list(result))

dou.columns=['订单id','商品ID','选购商品','商家编码','商品数量','订单应付金额','流量来源','达人昵称','广告渠道','流量类型','流量渠道','流量体裁','订单提交时间','平台实际承担优惠金额','达人实际承担优惠金额','店铺','订单状态','售后状态','福袋','电煮锅']

dou['成交金额_含优惠']=np.where( (( dou['订单状态']=="已签收")  | (dou['订单状态']=="待发货" ) | (dou['订单状态']=="已完成")  | (dou['订单状态']=="已发货")  | (dou['订单状态']=="已支付" )| ( dou['订单状态']=="部分发货" ))
                           &( dou['售后状态'] != "退款成功") & (dou['售后状态'] != "售后完成")  & (dou['售后状态'] != "退款完成")  & (dou['售后状态'] != "已全额退款" ),
                           dou['订单应付金额']+dou['平台实际承担优惠金额']+dou['达人实际承担优惠金额'],0)

dou['成交订单']=np.where( (( dou['订单状态']=="已签收")  | (dou['订单状态']=="待发货" ) | (dou['订单状态']=="已完成")  | (dou['订单状态']=="已发货")  | (dou['订单状态']=="已支付" )| ( dou['订单状态']=="部分发货" ))
                           &( dou['售后状态'] != "退款成功") & (dou['售后状态'] != "售后完成")  & (dou['售后状态'] != "退款完成")  & (dou['售后状态'] != "已全额退款" ),1,0)

dou['退款金额_含优惠']=np.where( (dou['售后状态'] == '退款成功')|(dou['售后状态'] =='退款完成')|(dou['售后状态'] =='已全额退款')|(dou['售后状态'] =='售后完成')|(dou['售后状态'] =='退货退款完成')|(dou['售后状态'] =='交易关闭'),
                           dou['订单应付金额']+dou['平台实际承担优惠金额']+dou['达人实际承担优惠金额'],0)


ld=pd.read_excel(r'G:\工作\2025年订单\9月\绩效\刘栋达人.xlsx',dtype={'抖音号名称':str})

lxx1=pd.read_excel(r'G:\工作\2025年订单\9月\绩效\罗欣欣达人1店.xlsx',dtype={'抖音号名称':str}) #(桶装)（袋装）
lxx2=pd.read_excel(r'G:\工作\2025年订单\9月\绩效\罗欣欣达人2店.xlsx',dtype={'抖音号名称':str}) #(土豆粉)


xhwh1=pd.read_excel(r'G:\工作\2025年订单\9月\绩效\夕湖文化.xlsx',dtype={'抖音号名称':str}) #(土豆粉)
hn1=pd.read_excel(r'G:\工作\2025年订单\9月\绩效\花妞.xlsx',dtype={'抖音号名称':str}) #(土豆粉)
tt1=pd.read_excel(r'G:\工作\2025年订单\9月\绩效\团团.xlsx',dtype={'抖音号名称':str}) #(土豆粉)
#ht1=pd.read_excel(r'G:\工作\2025年订单\9月\绩效\海图.xlsx',dtype={'抖音号名称':str}) #(火鸡面)

z1=pd.read_excel(r'G:\工作\2025年订单\9月\招商1.xlsx',dtype={'订单id':str})
z2=pd.read_excel(r'G:\工作\2025年订单\9月\招商2.xlsx',dtype={'订单id':str})
z3=pd.read_excel(r'G:\工作\2025年订单\9月\招商3.xlsx',dtype={'订单id':str})
z4=pd.read_excel(r'G:\工作\2025年订单\9月\招商4.xlsx',dtype={'订单id':str})
'''
l1=pd.read_excel(r'G:\工作\2025年订单\9月\ID点击购买率_抖1.xlsx',dtype={'商品编码':str})
l2=pd.read_excel(r'G:\工作\2025年订单\9月\ID点击购买率_抖2.xlsx',dtype={'商品编码':str})
l3=pd.read_excel(r'G:\工作\2025年订单\9月\ID点击购买率_抖3.xlsx',dtype={'商品编码':str})
l4=pd.read_excel(r'G:\工作\2025年订单\9月\ID点击购买率_抖4.xlsx',dtype={'商品编码':str})
'''

y1=pd.read_excel(r'G:\工作\2025年订单\9月\佣金1.xlsx',dtype={'订单id':str})
y2=pd.read_excel(r'G:\工作\2025年订单\9月\佣金2.xlsx',dtype={'订单id':str})
y3=pd.read_excel(r'G:\工作\2025年订单\9月\佣金3.xlsx',dtype={'订单id':str})
y4=pd.read_excel(r'G:\工作\2025年订单\9月\佣金4.xlsx',dtype={'订单id':str})
y=pd.concat([y1,y2,y3,y4])
#匹配佣金表，达人昵称
y_date=y.loc[:,['订单id','作者账号','预估佣金支出']] 
y_date['订单id']=y_date['订单id'].str.strip()

dou_date=pd.merge(dou,y_date,on='订单id',how='left')
#dou_date['达人昵称'].fill(dou_date['作者账号'],inplace=True)
dou_date['达人昵称']=np.where(dou_date['达人昵称']=='nan',dou_date['作者账号'],dou_date['达人昵称'])
#招商表
z1_date=z1.loc[:,['订单id','出单机构','预估服务费收入']]   
z2_date=z2.loc[:,['订单id','出单机构','预估服务费收入']]
z3_date=z3.loc[:,['订单id','出单机构','预估服务费收入']]
z4_date=z4.loc[:,['订单id','出单机构','预估服务费收入']]
zs=pd.concat([z1_date,z2_date,z3_date,z4_date])

dou_res=pd.merge(dou_date,zs,on='订单id',how='left')

#各投流链接,达人

lc_lj=['3752066905167495351','3754479940293230816']   #良辰链接
lc_dr=[]   #良辰达人

ld_lj=['3524421208374397998','3718871706278953300','3764710268731523333']   #刘栋单独链接

ld_dr=list(ld['抖音号名称'])

lxx1_lj=['3702367630972289253','3753012046921335096','3701090938974765405','3506957255406950391','3520740088860524483','3745946018349449594','3758543932670214558','3758958156538052831','3775109632327024899','3699396698997653933']#(罗欣欣达人）
lxx1_dr=list(lxx1['抖音号名称'])  #罗欣欣达人

lxx2_lj=['3748189374425203566','3751738608588030256','3752292420973363354','3737638082678227388','3628547567660489014','3694732441748111734','3754689465759694891','3712805099375034797','3674502993446830541','3766406205405266422']#(土豆粉达人)
lxx2_dr=list(lxx2['抖音号名称']) #罗欣欣土豆粉达人 
xhwh1_lj=['3751738608588030256','3687141961262825560','3758958156538052831','3723151364247912548','3701090938974765405'] #夕湖文化
xhwh1_dr=list(xhwh1['抖音号名称']) 
hn1_lj=['3701090938974765405','3758958156538052831','3687141961262825560','3775109632327024899'] #花妞千川——爆品侠
hn1_dr=list(hn1['抖音号名称']) 

tt1_lj=['3751738608588030256','3758958156538052831','3687141961262825560','3737638082678227388','3628547567660489014','3674502993446830541','3694732441748111734'] #团团别吃了
tt1_dr=list(tt1['抖音号名称']) 

def f(x,y,z,v,s,w,p):  #x: 链接 #y: 达人昵称 #z:选购商品 #v:广告渠道 #s:流量来源 #w:出单机构
    if  w in['武汉海大电子商务有限公司'] :   #罗欣欣运营账号
        return '嗨大'
    
    elif  w in['卓敏科技','紫金优选','瀚睿科技','红利传媒','来吉团队','金莎传媒'] :   #罗欣欣运营账号
        return '冷漠'
    
    
    elif (x in ld_lj ) and ( (y in ld_dr )  or (pd.isnull(y))):  #去掉招商达人，加上自然单
        return '刘栋单独链接'
    elif (x  in ['3751738608588030256'] ) and (y in ld_dr ) : 
        return '刘栋'
    
    elif  y in['harry5'] : 
        return 'harry5'
    
    elif  y in['喜喜小助理（直播返场）'] : 
        return '喜喜投流'
    elif  y in['张喜喜'] : 
        return '喜喜投流大号'

    elif  y in['美食挖掘机🍜','饱饱吃不饱','金金会发光','味蕾探险家','饭桶爱说笑','大馋丫头可可'] :   #罗欣欣运营账号
        return '罗欣欣账号'
    
    elif  y in['啦啦啦lalala','怪咖美食记','爱吃的光远','空壳少年','食光里的馋猫🐱','宛禾食品甄选号'] : #白鹏飞运营账号
        return '白鹏飞账号'
    elif (x in xhwh1_lj ) and (y in xhwh1_dr ) : 
        return '夕湖文化'
    
    elif (x in tt1_lj ) and (y in tt1_dr ) :
        return '罗欣欣'

    elif (x in lxx1_lj ) and (y in lxx1_dr ) : 
        return '罗欣欣'
    
    elif (x in lxx2_lj ) and (y in lxx2_dr ) : 
        return '罗欣欣'
    elif (x in hn1_lj ) and (y in hn1_dr ) : 
        return '代投花妞'
    
    elif (x  in ['3751738608588030256'] ) and (y in hn1_dr ) : 
        return '花妞'
    elif (x in lc_lj ) :
        return '莨辰'
    elif (x  in ['3768784577812889610'] )  : 
        return '木森'
    elif  x  in ['3735010733180715297','3741925082977665083']  : #罗欣欣单独链接
        return '罗欣欣账号'
    elif  x  in ['3723120728170692876','3723119143235485902','3741926701987398242']  : #白鹏飞单独链接
        return '白鹏飞账号'
      
    elif  x  in ['3714480675638870403','3741923461510070306','3550782895822129982','3526108849595662124','3699396698997653933']  : 
        return '共用链接自然单'
    
    #elif (v =='商品卡')and (x  in ['3712813994243915957','3735010733180715297','3713734480830267578','3712805099375034797','3712785316638228820','3738929837008617904','3746121270362767689','3741926278975062262','3714568370264342992','3743556622891483314','3719404361806381548','3741928376991088750','3738931359733907468','3668404150531653930','3686235918408221166','3699396698997653933','3686084716408209458']):
        #return '罗欣欣3店商品卡'
    
    #elif  (x  in ['3712813994243915957','3735010733180715297','3713734480830267578','3712805099375034797','3712785316638228820','3738929837008617904','3746121270362767689','3741926278975062262','3714568370264342992','3743556622891483314','3719404361806381548','3741928376991088750','3738931359733907468','3668404150531653930','3686235918408221166','3699396698997653933','3686084716408209458']):
        #return '罗欣欣账号'

    elif (v =='商品卡')and (('lcc' in z)  or ('lxx' in z)):
        return '罗欣欣3店商品卡'
    
    elif (('lcc' in z)  or ('lxx' in z)):
        return '罗欣欣账号'
    
    elif 'zs2' in z  or ( 'zz' in z) or ( 'zs' in z):
        return '代播'
    elif y=='宛禾食品旗舰店直播间' or  y=='宛禾牛宅':
        return '代播'
    elif y=='宛禾速食旗舰店自播间'  or  y=='宛禾食品旗舰店自播间':
        return '代播'
    
    elif y=='宛禾米线官方旗舰店' or ('z1' in z)  or (' Z1' in z):
        return '自播1'
    elif y=='宛禾米线速食官方旗舰店'  or y=='宛禾米线' or y=='宛禾速食官方旗舰店' or y=='宛禾速食直播间' or ('z3' in z)  or (' Z3' in z):
        return '自播3'
    elif y=='宛禾米线速食旗舰店' or ('lsf' in z) :
        return '螺蛳土豆粉直播间'
    elif y=='宛禾食品旗舰店' or ('z5' in z)  or ('Z5' in z) :
        return '自播5'
    elif y=='宛禾速食旗舰店' or y=='宛禾食品旗舰店甄选号' or ('z6' in z)  or ('Z6' in z):
        return '自播6'
    elif y=='宛禾官方旗舰店' or ('z8' in z)  or (' Z8' in z) :
        return '自播8'
    elif p=='抖音1' and ('dsp' in z) :
        return '自播1'
    elif p=='抖音2' and ('dsp' in z) :
        return '自播5'
    elif p=='抖音3' and ('dsp' in z) :
        return '自播8'
    elif p=='抖音4' and ('dsp' in z) :
        return '螺蛳土豆粉直播间'
    
    elif   (v =='商品卡'): #投流商品卡
        return '超级商品卡'
    
    elif pd.notnull(y):
        return '达人分发'
    elif s=='精选联盟':
        return '达人分发'
     
    else:
        return '自然单'
    

dou_res['投流账户'] =list(map(f,list(dou_res['商品ID']),list(dou_res['达人昵称']),list(dou_res['选购商品']),list(dou_res['广告渠道']),list(dou_res['流量来源']),list(dou_res['出单机构']),list(dou_res['店铺'])))


def m(x,y,z,v,s,w):  #x: 链接 #y: 达人昵称 #z:选购商品 #v:广告渠道 #s:流量来源 #w:出单机构
    if  w in['武汉海大电子商务有限公司'] :
        return '外部团队达人分发'
    
    elif  w in['卓敏科技','紫金优选','瀚睿科技','红利传媒','来吉团队','金莎传媒'] : 
        return '外部团队达人分发'
    
    
    
    elif (x in ld_lj ) and ( (y in ld_dr )  or (pd.isnull(y))): 
        return '内部短视频投流'
    elif (x  in ['3751738608588030256'] ) and (y in ld_dr ) : 
        return '内部短视频投流'
    
    elif  y in['harry5'] : 

        return '内部达人分发'
    
    elif  y in['喜喜小助理（直播返场）'] : 
        return '内部达人分发'
    elif  y in['张喜喜'] : 
        return '内部达人分发'
    elif  y in['美食挖掘机🍜','饱饱吃不饱','金金会发光','味蕾探险家','饭桶爱说笑','大馋丫头可可'] :   #罗欣欣运营账号
        return '内部短视频投流'
    
    elif  y in['啦啦啦lalala','怪咖美食记','爱吃的光远','空壳少年','食光里的馋猫🐱','宛禾食品甄选号'] : #白鹏飞运营账号
        return '内部短视频投流'
    
    elif (x in xhwh1_lj ) and (y in xhwh1_dr ) : 
        return '外部短视频投流'
    
    elif (x in tt1_lj ) and (y in tt1_dr ) : 
        return '内部达人分发'

    elif (x in lxx1_lj ) and (y in lxx1_dr ) : 
        return '内部达人分发'
    
    elif (x in lxx2_lj ) and (y in lxx2_dr ) : 
        return '内部达人分发'
    elif (x in hn1_lj ) and (y in hn1_dr ) : 
        return '外部短视频投流'
    elif (x  in ['3751738608588030256'] ) and (y in hn1_dr ) : 
        return '外部短视频投流'
    elif (x in lc_lj ) :
        return '外部短视频投流'
    
    elif  x  in ['3735010733180715297','3741925082977665083']  : #罗欣欣单独链接
        return '内部短视频投流'
    elif  x  in ['3723120728170692876','3723119143235485902','3741926701987398242']  : #白鹏飞单独链接
        return '内部短视频投流'
    
    elif  x  in ['3714480675638870403','3741923461510070306','3550782895822129982','3526108849595662124','3699396698997653933']  : 
        return '内部短视频投流'
    
    
    #elif (v =='商品卡')and (x  in ['3712813994243915957','3735010733180715297','3713734480830267578','3712805099375034797','3712785316638228820','3738929837008617904','3746121270362767689','3741926278975062262','3714568370264342992','3743556622891483314','3719404361806381548','3741928376991088750','3738931359733907468','3668404150531653930','3686235918408221166','3699396698997653933','3686084716408209458']):
        #return '超级商品卡'
   
    elif (v =='商品卡')and (('lcc' in z)  or ('lxx' in z)):
        return '超级商品卡'
    
    
    elif  ('lcc' in z)  or ('lxx' in z):
        return '内部短视频投流'
    
    elif (x  in ['3768784577812889610'] )  : 
        return '内部达人分发'
    
    elif 'zs2' in z  or ( 'zz' in z) or ( 'zs' in z):
        return '代播'
    elif y=='宛禾食品旗舰店直播间' or  y=='宛禾牛宅':
        return '代播'
    elif y=='宛禾速食旗舰店自播间'  or  y=='宛禾食品旗舰店自播间':
        return '代播'
    
    elif y=='宛禾米线官方旗舰店' or ('z1' in z)  or ('Z1' in z):
        return '自播'
    elif y=='宛禾米线速食官方旗舰店'  or y=='宛禾米线' or y=='宛禾速食官方旗舰店' or y=='宛禾速食直播间' or y=='宛禾速食' or ('z3' in z)  or ('Z3' in z):
        return '自播'
    elif y=='宛禾米线速食旗舰店' or ('lsf' in z) :
        return '自播'
    elif y=='宛禾食品旗舰店' or ('z5' in z)  or ('Z5' in z)or ('dsp' in z):
        return '自播'
    elif y=='宛禾速食旗舰店' or y=='宛禾食品旗舰店甄选号' or ('z6' in z)  or ('Z6' in z):
        return '自播'
    elif y=='宛禾官方旗舰店' or ('z8' in z)  or ('Z8' in z) :
        return '自播'
    
    elif   (v =='商品卡'): #投流商品卡
        return '超级商品卡'
    
    
    elif pd.notnull(y):
        return '内部达人分发'
    elif s=='精选联盟':
        return '内部达人分发'
    else:
        return '自然单'
    
dou_res['总渠道'] =list(map(m,list(dou_res['商品ID']),list(dou_res['达人昵称']),list(dou_res['选购商品']),list(dou_res['广告渠道']),list(dou_res['流量来源']),list(dou_res['出单机构'])))    


result=dou_res.groupby([dou_res['总渠道'],dou_res['投流账户']],as_index=False).agg(销售额_含优惠去退款=('成交金额_含优惠','sum'),订单数=('成交订单','sum'),退款金额=('退款金额_含优惠','sum'))

result.to_excel(r'G:\工作\2025年订单\9月\绩效\9月各渠道销售额月度1.xlsx')


date_SKU=dou_res
result_sku=date_SKU.groupby([date_SKU['商品ID'],date_SKU['总渠道'],date_SKU['投流账户']],as_index=False).agg(销售额_含优惠去退款=('成交金额_含优惠','sum'),订单数=('成交订单','sum'),退款金额=('退款金额_含优惠','sum'))

result_sku.to_excel(r'G:\工作\2025年订单\9月\绩效\9月投流销售额SKU月度1.xlsx')


result_bm=date_SKU.groupby([date_SKU['商家编码'],date_SKU['总渠道'],date_SKU['投流账户']],as_index=False).agg(销售额_含优惠去退款=('成交金额_含优惠','sum'),订单数=('成交订单','sum'),退款金额=('退款金额_含优惠','sum'))

result_bm.to_excel(r'G:\工作\2025年订单\9月\绩效\9月投流销售额编码月度1.xlsx')

dl=dou_res[(dou_res['成交订单'] != 0) &(dou_res['总渠道'] != '自播') & (dou_res['总渠道'] != '代播') & (dou_res['总渠道'] != '自然单')] 

dl.to_excel(r'G:\工作\2025年订单\9月\绩效\9月投流绩效单量明细月度1.xlsx')

#利润测算

dou_li=dou_res[(dou_res['成交订单'] != 0)]
dou_=dou_li.groupby([dou_li['店铺'],dou_li['总渠道'],dou_li['投流账户'],dou_li['商品ID'],dou_li['商家编码'],dou_li['福袋'],dou_li['电煮锅']],as_index=False).agg(订单实付=('订单应付金额','sum'),商品数量=('商品数量','sum'),平台优惠=('平台实际承担优惠金额','sum'),达人优惠=('达人实际承担优惠金额','sum'),成交金额=('成交金额_含优惠','sum'),佣金=('预估佣金支出','sum'),招商佣金=('预估服务费收入','sum'))
dou_.to_excel(r'G:\工作\SKU利润测算\2025年\9月\9月利润测算明细_抖音1.xlsx')

#判定sku属于某个渠道
'''
sku_qd=result_sku.groupby([result_sku['商品ID'],result_sku['总渠道']],as_index=False).agg( 销售额总 =('销售额_含优惠去退款','sum'))
sku_res=sku_qd.sort_values(['商品ID','销售额总'],ascending=[True,False])
sku_result=sku_res.groupby(['商品ID']).head(1)
#sku_result.to_excel(r'G:\结果\SKU流量情况周度.xlsx')
#匹配SKU的曝光人数，点击人数，成交人数

l=pd.concat([l1,l2,l3,l4])
res_l=l.loc[:,['商品编码','成交人数','商品曝光人数','商品点击人数']]
res_l.columns=['商品ID','成交人数','商品曝光人数','商品点击人数']

sku_info=pd.merge(sku_result,res_l,on=['商品ID'],how='left')

rus_hz=pd.merge(sku_qd,sku_info,on=['商品ID','总渠道'],how='left')

rus_hz.to_excel(r'G:\工作\2025年订单\9月\绩效\9月SKU流量情况月度.xlsx')
'''

#orders=dou_res[(dou_res['总渠道'] == '内部团队达人分发') ] 

orderid = dou_res.loc[:,['订单id','总渠道','订单提交时间','店铺']]
# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="hui123456", db='dbtest')
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用execute()方法执行SQL查询
#cursor.execute("create database dbtest charset=utf8;")
# 使用 fetchone() 方法获取单条数据

sqll=('insert into orderID_qd values (%s,%s,%s,%s)')

for val in orderid.values:
    cursor.execute(sqll,tuple(val))

db.commit()
db.close()


